﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

/// <summary>
/// Summary description for Fund
/// </summary>
public class Fund
{
	public Fund(){}
    public Fund(string policyNo, string code) {
        this.code = code;
        this.policyNo = policyNo;
    }

    public string code {set;get;}
    public string currency { set; get; }
    public string name { set; get; }
    public decimal allocation { set; get; }
    public string policyNo { set; get; }
    public bool isActive { set; get; }
    public decimal investUnits { set; get; }
    public decimal insuranceChargeUnits { set; get; }
    public decimal otherChargesUnits { set; get; }
    public DateTime lastTradeDateTime { set; get; }
    public FundPrice currentFundPrice { set; get; }
    public decimal bidPrice { set; get; }
    public decimal offerPrice { set; get; }
    public DateTime valuationDate { set; get; }

    public decimal getAccountValue(DateTime date)
    {
        var accountValue = this.investUnits * (this.findFundPrice(date).bidPrice);
        return accountValue;
    }


    //Set all atributes based on policy no. and code retrieved from database(R_POLICY_FUND) to the new object
    public void load(string policyNo, string code)
    {
        Fund fund = Fund.getFund(policyNo, code);
        this.code = fund.code;
        this.name = fund.name;
        this.policyNo = fund.policyNo;
        this.allocation = fund.allocation;
        this.investUnits = fund.investUnits;
        this.insuranceChargeUnits = fund.insuranceChargeUnits;
        this.otherChargesUnits = fund.otherChargesUnits;
        this.lastTradeDateTime = fund.lastTradeDateTime;
        this.isActive = fund.isActive;
    }

    //Retrieve data from R_POLICY_FUND
    public static Fund getFund(string policy, string code){
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT P.fund_code, R.fund_name, policy_no, allocation, invest_units, insurance_charge_units,other_charges_units, last_trade_date,status" +
                      " FROM R_POLICY_FUND P, R_FUND R" +
                      " WHERE P.FUND_CODE = R.FUND_CODE AND POLICY_NO =@p AND P.fund_code=@f";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@p", (object)policy ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@f", (object)code ?? DBNull.Value));

        SqlDataReader dr = cmd.ExecuteReader();
        Fund fund = new Fund();


        if (dr.Read())
        {
            fund.code = dr.GetString(0);
            fund.name = dr.GetString(1);
            fund.policyNo = dr.GetString(2);
            fund.allocation = dr.GetDecimal(3);
            fund.investUnits = dr.GetDecimal(4);
            fund.insuranceChargeUnits = dr.GetDecimal(5);
            fund.otherChargesUnits = dr.GetDecimal(6);
            fund.lastTradeDateTime = dr.GetDateTime(7);
            string status = dr.GetString(8);
            fund.currency = "SGD";
            
            if(status == "ACTIVE"){
                fund.isActive = true;
            }else{
                fund.isActive = false;
            }
            
        }

        dr.Close();
        conn.Close();
        
        return fund;
    }

    
    public static Dictionary<string,string> getFundCodeNameMap()
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT fund_code, fund_name " +
                      " FROM R_FUND";

        SqlCommand cmd = new SqlCommand(sql, conn);

        SqlDataReader dr = cmd.ExecuteReader();
        Dictionary<string, string> map = new Dictionary<string, string>();


        while (dr.Read())
        {
            map.Add(dr.GetString(0),dr.GetString(1));
        }

        dr.Close();
        conn.Close();

        return map;
    }

    public static Dictionary<string, string> getFundNameCodeMap()
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT fund_code, fund_name " +
                      " FROM R_FUND";

        SqlCommand cmd = new SqlCommand(sql, conn);

        SqlDataReader dr = cmd.ExecuteReader();
        Dictionary<string, string> map = new Dictionary<string, string>();


        while (dr.Read())
        {
            map.Add(dr.GetString(1), dr.GetString(0));
        }

        dr.Close();
        conn.Close();

        return map;
    }

    public static List<Fund> getAllPortfolioFunds(string policy)
    {
        return getPortfolioFunds(policy, "ALL");
    }

    public static List<Fund> getActivePortfolioFunds(string policy)
    {
        return getPortfolioFunds(policy, "ACTIVE");
    }

    public static List<Fund> getInactivePortfolioFunds(string policy)
    {
        return getPortfolioFunds(policy, "INACTIVE");
    }

    public static void addNewFund(string policyNo, string fundCode)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "insert R_POLICY_FUND " +
                          " (policy_no, fund_code, allocation, invest_units, insurance_charge_units, plus_rider_charge_units, other_charges_units, last_trade_date, status)" +
                    " Values(@policy_no, @fund_code, @allocation, @invest_units, @insurance_charge_units, @plus_rider_charge_units, @other_charges_units, @last_trade_date, @status)";

        SqlCommand cmd = new SqlCommand(sql, conn);

        decimal zero = 0.0000m;
        cmd.Parameters.Add(new SqlParameter("@policy_no", (object)policyNo ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@fund_code", (object)fundCode ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@allocation", zero));
        cmd.Parameters.Add(new SqlParameter("@invest_units", zero));
        cmd.Parameters.Add(new SqlParameter("@insurance_charge_units", zero));
        cmd.Parameters.Add(new SqlParameter("@plus_rider_charge_units", zero));
        cmd.Parameters.Add(new SqlParameter("@other_charges_units", zero));
        cmd.Parameters.Add(new SqlParameter("@last_trade_date", (object)DateTime.Now ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@status", "ACTIVE"));

        cmd.ExecuteNonQuery();
        conn.Close();

    }

    //Retrieve all fund information for the policy
    public static List<Fund> getPortfolioFunds(string policy, string ALL_ACTIVE_INACTIVE)
    {
        var funds = new List<Fund>();
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string activeCondition = "";

        if (ALL_ACTIVE_INACTIVE == "ACTIVE")
        {
            activeCondition = " and A.status='ACTIVE'" ;

        }
        else if (ALL_ACTIVE_INACTIVE == "INACTIVE")
        {
            activeCondition = " and A.status='INACTIVE'";
        }
        else
        {
            activeCondition = "";
        }
       
        string sql = "select A.fund_code,B.fund_name,invest_units, insurance_charge_units, other_charges_units,last_trade_date,allocation " +
                     "from R_POLICY_FUND A, R_FUND B " +
                     "where A.fund_code = B.fund_code" +
                     activeCondition +
                     "and policy_no ='" + policy + "'";

        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            Fund fund = new Fund();
            fund.policyNo = policy;
            fund.code = dr.GetString(0);
            fund.name = dr.GetString(1);
            fund.investUnits = dr.GetDecimal(2);
            fund.insuranceChargeUnits = dr.GetDecimal(3);
            fund.otherChargesUnits = dr.GetDecimal(4);
            fund.lastTradeDateTime = dr.GetDateTime(5);
            fund.allocation = dr.GetDecimal(6);
            fund.currency = "SGD";
            funds.Add(fund);
        }

        dr.Close();

        cmd.ExecuteNonQuery();
        conn.Close();

        return funds;
    }

   
    public FundPrice findFundPrice(DateTime date)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT fund_code, valuation_date, bid_price, offer_price, currency" +
                      " FROM R_FUND_DAILY_PRICE " +
                      " WHERE (CONVERT(VARCHAR(8), valuation_date, 103) = " +
                      " CONVERT(VARCHAR(8), @d , 103)) and fund_code = @f";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@f", (object)this.code ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@d", (object)date ?? DBNull.Value));
 
        SqlDataReader dr = cmd.ExecuteReader();

        FundPrice fundPrice = new FundPrice();

        if(dr.Read())
        {         
            fundPrice.fundCode = dr.GetString(0);
            fundPrice.valuationDate = dr.GetDateTime(1);
            fundPrice.bidPrice = dr.GetDecimal(2);
            fundPrice.offerPrice = dr.GetDecimal(3);
            fundPrice.currency = dr.GetString(4);     
        }

        dr.Close();
        conn.Close();

        return fundPrice;
    }

    // insert into R_FUND_TRANSACTION AND R_POLICY_FUND
    public decimal sell(decimal fundUnit,DateTime sellDate)
    {
        checkIsUnitEnough(fundUnit);
        
        FundPrice presentPrice = findFundPrice(sellDate);
        decimal accountValue = fundUnit * presentPrice.bidPrice;
        this.investUnits -= fundUnit;

        insertFundTransation("SELL", fundUnit, presentPrice.bidPrice);
        updateInvestUnits(this.investUnits);

        return accountValue;
    }


    // insert into R_FUND_TRANSACTION AND R_POLICY_FUND
    public decimal buy(decimal fundUnit, DateTime sellDate)
    {
        FundPrice presentPrice = findFundPrice(sellDate);
        decimal accountValue = fundUnit * presentPrice.offerPrice;
        this.investUnits += fundUnit;

        insertFundTransation("BUY", fundUnit, presentPrice.offerPrice);
        updateInvestUnits(this.investUnits);

        return accountValue;
    }

    //Fund Switch is based on bid-bid basis
    public decimal buyAtBidPrice(decimal fundUnit, DateTime sellDate)
    {
        FundPrice presentPrice = findFundPrice(sellDate);
        decimal accountValue = fundUnit * presentPrice.bidPrice;
        this.investUnits += fundUnit;

        insertFundTransation("BUY", fundUnit, presentPrice.bidPrice);
        updateInvestUnits(this.investUnits);

        return accountValue;
    }



    public void insertFundTransation(String tradeType,decimal unit, decimal price)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "insert r_fund_transaction " +
                          " (policy_no, fund_code, trade_time, trade_type, unit, price)" +
                    " Values(@policy_no, @fund_code, @trade_time, @trade_type, @unit, @price)";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@policy_no", (object)this.policyNo ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@fund_code", (object)this.code ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@trade_time", (object)DateTime.Now?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@trade_type", (object)tradeType?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@unit", (object) unit ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@price", (object)price ?? DBNull.Value));

        cmd.ExecuteNonQuery();
        conn.Close();

    }

    public void checkIsUnitEnough(decimal fundUnit)
    {
        if (this.investUnits - fundUnit < 0)
        {
            throw new RetirementException(RetirementException.WITHDRAWAL_OVER_FUND_MIN_BALANCE);
        }
    }

    public void checkOverWithdrawal(decimal amount)
    {
        decimal remeanValue = getAccountValue(DateTime.Now) - amount;
        if (remeanValue < 500)
        {
            throw new RetirementException(RetirementException.WITHDRAWAL_OVER_FUND_MIN_BALANCE);
        }
    }

    public void updateInvestUnits(decimal newUnits)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "UPDATE R_POLICY_FUND SET INVEST_UNITS =  " +newUnits +
                    " ,LAST_TRADE_DATE=GETDATE() WHERE POLICY_NO='"+this.policyNo+"' AND FUND_CODE='"+this.code+"'";
        
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        
        conn.Close();
    }

    public void updateBalance(decimal investAmount, decimal insuranceChargeUnits, decimal plusRiderChargeUnits, decimal otherChargesUnits)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "UPDATE R_POLICY_FUND SET " +
                    " INVEST_UNITS =  " + investAmount +
                    " ,PLUS_RIDER_CHARGE_UNITS =  " + plusRiderChargeUnits +
                    " ,INSURANCE_CHARGE_UNITS =  " + insuranceChargeUnits +
                    " ,OTHER_CHARGES_UNITS =  " + otherChargesUnits +
                    " ,LAST_TRADE_DATE = GETDATE() " +
                    " WHERE POLICY_NO='" + this.policyNo + "' AND FUND_CODE='" + this.code + "'";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();

        conn.Close();
    }

    public void insertPolicyFund()
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "INSERT R_POLICY_FUND(fund_code, policy_no, invest_units, insurance_charge_units, other_charges_units) " +
                    " VALUES (@c,@p,@invest,@charge,@other)";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@c", (object)this.code ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@p", (object)this.policyNo ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@invest", (object)this.investUnits ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@charge", (object)this.insuranceChargeUnits ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@other", (object)this.otherChargesUnits ?? DBNull.Value));

        cmd.ExecuteNonQuery();
        conn.Close();

    }

    public void disable()
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "UPDATE R_POLICY_FUND SET " +
                    " STATUS = 'INACTIVE' " + 
                    " WHERE POLICY_NO='" + this.policyNo + "' AND FUND_CODE='" + this.code + "'";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();

        conn.Close();
        this.isActive = false;
    }

    public void updateAllocation(decimal allocationPercentage)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "UPDATE R_POLICY_FUND SET ALLOCATION =  " + allocationPercentage +
                    " WHERE POLICY_NO='" + this.policyNo + "' AND FUND_CODE='" + this.code + "'";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();

        conn.Close();
    }

    public void enable()
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "UPDATE R_POLICY_FUND SET " +
                    " STATUS = 'ACTIVE' " +
                    " WHERE POLICY_NO='" + this.policyNo + "' AND FUND_CODE='" + this.code + "'";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.ExecuteNonQuery();

        conn.Close();
        this.isActive = true;
    }


    public static FundPrice findFundPrice(string code, DateTime date)
    {
        SqlConnection conn = DBManager.getSqlConnection();
        conn.Open();

        string sql = "SELECT fund_code, valuation_date, bid_price, offer_price, currency" +
                      " FROM R_FUND_DAILY_PRICE " +
                      " WHERE (CONVERT(VARCHAR(8), valuation_date, 103) = " +
                      " CONVERT(VARCHAR(8), @d , 103)) and fund_code = @f";

        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add(new SqlParameter("@f", (object)code ?? DBNull.Value));
        cmd.Parameters.Add(new SqlParameter("@d", (object)date ?? DBNull.Value));

        SqlDataReader dr = cmd.ExecuteReader();

        FundPrice fundPrice = new FundPrice();

        if (dr.Read())
        {
            fundPrice.fundCode = dr.GetString(0);
            fundPrice.valuationDate = dr.GetDateTime(1);
            fundPrice.bidPrice = dr.GetDecimal(2);
            fundPrice.offerPrice = dr.GetDecimal(3);
            fundPrice.currency = dr.GetString(4);
        }

        dr.Close();
        conn.Close();

        return fundPrice;
    }



}